package com.situ.company.department.dao.Inf;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.situ.company.department.dao.IDepartmentDao;
import com.situ.company.department.model.DepartmentModel;
import com.situ.company.util.JDBCUtil;

public class Department2Dao implements IDepartmentDao {
	private static String table = "st_company.department";
	private static String cols = "code,name,tel";
	@Override
	public Integer insert(DepartmentModel model) {
		StringBuffer sql = new StringBuffer("insert into ");
		sql.append(table).append(" (").append(cols).append(") values(?,?,?)");
		return JDBCUtil.update(sql.toString(), model.getCode(),model.getName(),model.getTel());
	}
	
	@Override
	public Integer delete(DepartmentModel model) {
		StringBuffer sql = new StringBuffer("delete from ");
		sql.append(table);// where code = ?
		List<Object> vals = appendWhere(sql, model);
		return JDBCUtil.update(sql.toString(),vals);
	}
	
	@Override
	public Integer update(DepartmentModel model) {
		StringBuffer sql = new StringBuffer("update ");
		sql.append(table);// set name=?,tel=? where code=?
		List<Object> vals = appendSet(sql, model);
		return JDBCUtil.update(sql.toString(),vals);
	}
	
	@Override
	public List<DepartmentModel> selectList(DepartmentModel model) {
		StringBuffer sql = new StringBuffer("select ");
		sql.append(cols).append(",(select count(id) from st_company.employee where depCode=department.code) count").append(" from ").append(table);
		List<Object> vals = appendWhere(sql, model);
		Map<String,String> fields = new HashMap<>();
		fields.put("code", "code");
		fields.put("name", "name");
		fields.put("tel", "tel");
		fields.put("count", "count");
		return JDBCUtil.query(sql.toString(), vals, DepartmentModel.class, fields);
	}
	
	@Override
	public int selectCount(DepartmentModel model) {
		StringBuffer sql = new StringBuffer("select count(id) from ");
		sql.append(table);
		List<Object> vals = appendWhere(sql, model);
		return JDBCUtil.queryInt(sql.toString(), vals);
	}
	
	private List<Object> appendWhere(StringBuffer sql, DepartmentModel model) {
		sql.append(" where 1=1");
		List<Object> objs = new ArrayList<Object>();
		String code = model.getCode();
		if (code != null && !code.trim().isEmpty()) {// !"".equals(code.trim())
			sql.append(" and code like ?");
			objs.add(code);
		}
		String name = model.getName();
		if (name != null && !name.trim().isEmpty()) {
			sql.append(" and name like ?");
			objs.add(name);
		}
		String order = model.getOrderby();
		if (order != null && !order.trim().isEmpty()) {
			sql.append(" order by ? ").append(order);
		}
		if (model.isPageOn()) {
			sql.append(" limit ?,? ");
			objs.add(model.getRowStart());
			objs.add(model.getRowCount());
		}
		return objs;
	}
	
	private List<Object> appendSet(StringBuffer sql, DepartmentModel model) {
		sql.append(" set id=id ");
		List<Object> objs = new ArrayList<Object>();

		String name = model.getName();
		if (name != null) {
			sql.append(",name = ? ");
			objs.add(name);
		}
		String tel = model.getTel();
		if (tel != null) {
			sql.append(",tel = ? ");
			objs.add(tel);
		}
		String code = model.getCode();
		sql.append("where code = ?");
		objs.add(code);
		return objs;
	}
	
	
}
